# 比较表格数据


from openpyxl import load_workbook
from openpyxl.utils.cell import range_boundaries
from openpyxl.styles import Font, PatternFill, Alignment

from inner_util import try_file_path, read_from_wb, read_from_xlsx


DEFAULT_FONT = Font(bold=False, color="000000", size=9)
RED_FONT = Font(bold=True, color="FF0000", size=9)


YELLOW_FILL = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
WHITE_FILL = PatternFill(start_color="FFFFff", end_color="FFFFff", fill_type="solid")

HAS_WRAP = Alignment(wrap_text=True)


class Spreadsheet:
    """
    文档类
    """

    def __init__(self, file_path) -> None:
        try:
            self.wb = load_workbook(file_path, data_only=False)
        except FileNotFoundError as e:
            print("\033[41mError\033[m", e)
            quit()

        self.mod_cells = []
        self.ws = self.wb.active

    def get_data_rows(self):
        return read_from_wb(self.wb)

    def set_range_style(
        self, range, font=DEFAULT_FONT, fill=WHITE_FILL, alignment=None
    ):
        min_col, min_row, max_col, max_row = range_boundaries(range)
        for row in self.ws.iter_rows(min_row, max_row, min_col, max_col):
            for c in row:
                self.set_cell_style(c.coordinate, font, fill, alignment)

    def set_cell_value(self, cell, value):
        self.ws[cell].value = value

    def set_cell_style(self, cell, font=DEFAULT_FONT, fill=WHITE_FILL, alignment=None):
        self.ws[cell].font = font
        self.ws[cell].fill = fill
        if alignment is not None:
            self.ws[cell].alignment = alignment

    def save_as(self, target_path):
        self.wb.save(try_file_path(target_path))

    def merge(self, other_file, start_row=0, max_col=0):
        ws = self.ws
        other_data_rows = read_from_xlsx(other_file, 0, start_row, max_col)
        for row in other_data_rows:
            ws.append(row)

    def diff(self, other_spreedsheet, range_str):
        """
        比较表格某个区域的数据，返回修改的单元格坐标
        此函数修改原始表格，可以多次比较，返回最新修改
        仅用于一张表格，有不同的数据来源，各自修改各自的内容，其他人的数据不同的情况。
        """
        ws = self.ws
        other_ws = other_spreedsheet.wb.active

        other_ws_mod_cells = []
        min_col, min_row, max_col, max_row = range_boundaries(range_str)
        for row in ws.iter_rows(min_row, max_row, min_col, max_col):
            for cell in row:
                # 要判断是否已经是修改过的单元格，避免重复修改
                # 修改过的单元格就是别的表已经改过了，不能再改
                if cell.coordinate in self.mod_cells:
                    continue
                elif cell.value != other_ws[cell.coordinate].value:
                    print(
                        f"Diff {cell.coordinate} {cell.value} -> {other_ws[cell.coordinate].value}"
                    )
                    cell.value = other_ws[cell.coordinate].value
                    # 这里有修改，要记录在原始表的mod_cells中，不能再次修改
                    # 正常情况下，每一次比较修改的数据都不是相同的
                    self.mod_cells.append(cell.coordinate)
                    other_ws_mod_cells.append(cell.coordinate)

        return other_ws_mod_cells

    def dry_diff(self, other_spreedsheet, range_str):
        """
        比较表格某个区域的数据，返回修改的单元格坐标.
        此函数不修改原始表格，仅比较
        """
        ws = self.ws
        other_ws = other_spreedsheet.wb.active

        other_ws_mod_cells = []
        min_col, min_row, max_col, max_row = range_boundaries(range_str)
        for row in ws.iter_rows(min_row, max_row, min_col, max_col):
            for cell in row:
                # 要判断是否已经是修改过的单元格，避免重复修改
                # 修改过的单元格就是别的表已经改过了，不能再改
                if cell.coordinate in self.mod_cells:
                    continue
                elif cell.value != other_ws[cell.coordinate].value:
                    print(
                        f"Diff {cell.coordinate} {cell.value} -> {other_ws[cell.coordinate].value}"
                    )

                    # 这里记录修改的位置，老值，新值
                    mod_detail = (
                        cell.coordinate,
                        cell.value,
                        other_ws[cell.coordinate].value,
                    )

                    # 这里不加入修改类本身的mod_cells，因为不修改表格，仅仅是比较
                    other_ws_mod_cells.append(mod_detail)

        return other_ws_mod_cells
